"""
需求管理API路由
"""
from typing import Any, Optional, List, Dict
from fastapi import APIRouter, Depends, HTTPException, Query, Request, UploadFile, File
try:
    from fastapi import status as http_status_module
except ImportError:
    from starlette import status as http_status_module  # 后备导入
from fastapi.responses import StreamingResponse  # pyright: ignore[reportMissingImports]
from motor.motor_asyncio import AsyncIOMotorDatabase
from bson import ObjectId
from datetime import datetime, timedelta
import io
from urllib.parse import quote
import logging
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter

from app.api.deps import get_db, get_current_user
from app.models.requirement import (
    RequirementPriority,
)
# RequirementType 和 RequirementSource 已移除，改为使用统一配置管理
from app.schemas.requirement import (
    RequirementCreate,
    RequirementUpdate,
    RequirementResponse,
    RequirementStatusUpdate,
    RequirementBulkUpdate
)
from pydantic import BaseModel, Field
from typing import List as TypingList
from app.core.response import paginated_response, error_response, ErrorTypes, success_response, ApiResponse
from app.services.requirement_service import RequirementService
from app.core.permissions import Permissions
from app.core.config import settings
from app.services.attachment_service import AttachmentService
from app.models.version import RelationType
from app.services.notification_service import NotificationService
from app.models.notification import NotificationCreate, NotificationType, RelatedObjectType
import asyncio

# 初始化需求服务实例
async def get_requirement_service(db=Depends(get_db)):
    return RequirementService(db)

router = APIRouter()
attachment_service = AttachmentService()
logger = logging.getLogger(__name__)

REQUIREMENT_EXPORT_HEADERS = [
    "需求ID", "标题", "描述", "项目", "项目ID", "状态", "优先级", "需求类型", "需求来源",
    "负责人", "报告人", "预估工时(人天)", "开始日期", "截止日期", "标签", "关联版本", "版本ID",
    "验收标准", "业务价值", "创建时间", "更新时间"
]

REQUIREMENT_IMPORT_TEMPLATE_DEFINITION = [
    {"header": "标题（必填）", "key": "标题", "required": True},
    {"header": "项目（必填，项目名称）", "key": "项目", "required": True},
    {"header": "项目ID（可选，优先使用）", "key": "项目ID", "required": False},
    {"header": "负责人（可选）", "key": "负责人", "required": False},
    {"header": "报告人（可选，默认当前用户）", "key": "报告人", "required": False},
    {"header": "状态（可选）", "key": "状态", "required": False},
    {"header": "优先级（可选）", "key": "优先级", "required": False},
    {"header": "需求类型（可选）", "key": "需求类型", "required": False},
    {"header": "需求来源（可选）", "key": "需求来源", "required": False},
    {"header": "开始日期(YYYY-MM-DD，可选)", "key": "开始日期(YYYY-MM-DD)", "required": False},
    {"header": "截止日期(YYYY-MM-DD，可选)", "key": "截止日期(YYYY-MM-DD)", "required": False},
    {"header": "预估工时(人天，可选)", "key": "预估工时(人天)", "required": False},
    {"header": "创建时间(YYYY-MM-DD，可选)", "key": "创建时间(YYYY-MM-DD)", "required": False},
    {"header": "关联版本(逗号分隔，版本名称，可选)", "key": "关联版本(逗号分隔)", "required": False},
    {"header": "版本ID(逗号分隔，优先使用，可选)", "key": "版本ID(逗号分隔)", "required": False},
    {"header": "标签(逗号分隔，可选)", "key": "标签(逗号分隔)", "required": False},
    {"header": "描述（可选）", "key": "描述", "required": False},
    {"header": "验收标准（可选）", "key": "验收标准", "required": False},
    {"header": "业务价值（可选）", "key": "业务价值", "required": False}
]

def normalize_import_header(value: Any) -> str:
    if value is None:
        return ""
    text = str(value).strip()
    markers = [
        "（必填）", "(必填)",
        "（可选）", "(可选)",
        "（可选，默认当前用户）", "(可选，默认当前用户)",
    ]
    for marker in markers:
        text = text.replace(marker, "")
    text = text.replace("，默认当前用户", "")
    text = text.replace(",默认当前用户", "")
    text = text.replace("，可选", "")
    text = text.replace(",可选", "")
    # 压缩多余空格
    text = " ".join(text.split())
    return text.strip()

REQUIREMENT_IMPORT_HEADER_KEY_MAP: Dict[str, str] = {}
for item in REQUIREMENT_IMPORT_TEMPLATE_DEFINITION:
    header_text = item["header"]
    key = item["key"]
    REQUIREMENT_IMPORT_HEADER_KEY_MAP[header_text] = key
    REQUIREMENT_IMPORT_HEADER_KEY_MAP[key] = key
    normalized = normalize_import_header(header_text)
    REQUIREMENT_IMPORT_HEADER_KEY_MAP[normalized] = key

REQUIREMENT_IMPORT_TEMPLATE_HEADERS = [item["header"] for item in REQUIREMENT_IMPORT_TEMPLATE_DEFINITION]
REQUIREMENT_IMPORT_REQUIRED_KEYS = [item["key"] for item in REQUIREMENT_IMPORT_TEMPLATE_DEFINITION if item["required"]]

@router.get("/", response_model=None, summary="获取需求列表（分页）")
async def get_requirements(
    request: Request,
    page: int = Query(1, ge=1, description="页码"),
    size: int = Query(20, ge=1, le=200, description="每页大小"),
    project_id: Optional[str] = Query(None, description="项目ID筛选"),
    assignee_id: Optional[str] = Query(None, description="负责人筛选"),
    reporter_id: Optional[str] = Query(None, description="报告人筛选"),
    status: Optional[str] = Query(None, description="状态筛选"),
    priority: Optional[RequirementPriority] = Query(None, description="优先级筛选"),
    requirement_type: Optional[str] = Query(None, description="需求类型筛选（从类型管理获取）"),
    source: Optional[str] = Query(None, description="需求来源筛选（从来源管理获取）"),
    created_at_start: Optional[str] = Query(None, description="创建时间开始"),
    created_at_end: Optional[str] = Query(None, description="创建时间结束"),
    has_version: Optional[bool] = Query(None, description="是否已关联版本"),
    tags: Optional[str] = Query(None, description="标签筛选（逗号分隔）"),
    version_id: Optional[str] = Query(None, description="版本ID筛选（通过关联表）"),
    parent_id: Optional[str] = Query(None, description="父需求ID筛选（为空字符串时只返回顶级需求）"),
    tree: bool = Query(False, description="是否返回树形结构"),
    sort_by: str = Query("created_at", description="排序字段"),
    sort_order: str = Query("desc", regex="^(asc|desc)$", description="排序方向"),
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """获取需求列表"""
    try:
        from app.core.response import paginated_response
        
        # 调用服务层方法，所有参数转换逻辑都在service层处理
        result = await requirement_service.get_requirements_as_dict(
            user=current_user,
            page=page,
            size=size,
            project_id=project_id,
            assignee_id=assignee_id,
            reporter_id=reporter_id,
            status=status,
            priority=priority,
            requirement_type=requirement_type,
            source=source,
            tags=tags,
            created_at_start=created_at_start,
            created_at_end=created_at_end,
            parent_id=parent_id if parent_id else None,
            sort_by=sort_by,
            sort_order=sort_order,
            tree=tree,
            has_version=has_version,
            version_id=version_id
        )
        
        # 树形结构模式下，返回树形数据，不分页
        if tree:
            return success_response(
                data={
                    "items": result["items"],
                    "total": result["total"],
                    "tree": True
                },
                message="获取需求树形结构成功",
                request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
            )
        
        # 使用统一的分页响应格式
        return paginated_response(
            items=result["items"],
            total=result["total"],
            page=result["page"],
            size=result["size"],
            message="获取需求列表成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        # 重新抛出HTTP异常
        raise
    except Exception as e:
        import traceback
        import logging
        logger = logging.getLogger(__name__)
        logger.error(f"获取需求列表失败: {str(e)}", exc_info=True)
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"获取需求列表失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.get("/count", response_model=ApiResponse, summary="获取需求总数")
async def get_requirements_count(
    project_id: Optional[str] = Query(None, description="项目ID筛选"),
    assignee_id: Optional[str] = Query(None, description="负责人筛选"),
    reporter_id: Optional[str] = Query(None, description="报告人筛选"),
    status: Optional[str] = Query(None, description="状态筛选"),
    priority: Optional[RequirementPriority] = Query(None, description="优先级筛选"),
    requirement_type: Optional[str] = Query(None, description="需求类型筛选（从类型管理获取）"),
    source: Optional[str] = Query(None, description="需求来源筛选（从来源管理获取）"),
    created_at_start: Optional[str] = Query(None, description="创建时间开始"),
    created_at_end: Optional[str] = Query(None, description="创建时间结束"),
    has_version: Optional[bool] = Query(None, description="是否已关联版本"),
    tags: Optional[str] = Query(None, description="标签筛选（逗号分隔）"),
    version_id: Optional[str] = Query(None, description="版本ID筛选"),
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """获取需求总数"""
    try:
        # 调用服务层方法，所有参数转换逻辑都在service层处理
        count = await requirement_service.get_requirements_count(
            user=current_user,
            project_id=project_id,
            assignee_id=assignee_id,
            reporter_id=reporter_id,
            status=status,
            priority=priority,
            requirement_type=requirement_type,
            source=source,
            tags=tags,
            created_at_start=created_at_start,
            created_at_end=created_at_end,
            has_version=has_version,
            version_id=version_id
        )
        
        return success_response(data={"count": count}, message="获取需求总数成功")
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取需求总数失败: {str(e)}", exc_info=True)
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"获取需求总数失败: {str(e)}"
        )

@router.get("/export-template", summary="下载需求导入模板")
async def download_requirement_template(
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    user_permissions = current_user.get("permissions", [])
    if Permissions.REQUIREMENT_READ not in user_permissions and Permissions.ADMIN not in user_permissions:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_403_FORBIDDEN', 403),
            detail="Not enough permissions to download template"
        )

    wb = Workbook()
    ws = wb.active
    ws.title = "需求导入模板"

    ws.append(REQUIREMENT_IMPORT_TEMPLATE_HEADERS)

    header_font = Font(bold=True, color="FFFFFF")
    required_fill = PatternFill(start_color="DC2626", end_color="DC2626", fill_type="solid")
    optional_fill = PatternFill(start_color="2563EB", end_color="2563EB", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")

    for col_num, column in enumerate(REQUIREMENT_IMPORT_TEMPLATE_DEFINITION, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = required_fill if column["required"] else optional_fill
        cell.alignment = header_alignment
        ws.column_dimensions[cell.column_letter].width = max(18, len(str(column["header"])) + 6)

    # 获取状态（中文名称）
    states = await requirement_service.get_module_states("requirement")
    status_display_values = sorted({getattr(state, "name", "") for state in states if getattr(state, "name", "")})
    priority_display_values = ["低", "中", "高", "严重", "紧急"]
    type_display_values = ["功能性需求", "非功能性需求", "业务需求", "技术需求", "接口需求", "性能需求", "安全需求", "可用性需求"]
    source_display_values = ["客户", "利益相关者", "市场调研", "竞争对手", "内部", "法规要求", "技术债务"]

    def add_validation(column_index: int, values: List[str]) -> None:
        if not values:
            return
        formula = '"' + ",".join(values) + '"'
        dv = DataValidation(type="list", formula1=formula, allow_blank=True, showDropDown=True)
        col_letter = get_column_letter(column_index)
        dv.ranges.add(f"{col_letter}2:{col_letter}1048576")
        ws.add_data_validation(dv)

    for idx, column in enumerate(REQUIREMENT_IMPORT_TEMPLATE_DEFINITION, start=1):
        if column["key"] == "状态":
            add_validation(idx, status_display_values)
        elif column["key"] == "优先级":
            add_validation(idx, priority_display_values)
        elif column["key"] == "需求类型":
            add_validation(idx, type_display_values)
        elif column["key"] == "需求来源":
            add_validation(idx, source_display_values)

    sample_row = [
        "示例需求", "示例项目", "", "张三", "李四", "草稿", "中", "功能性需求", "内部",
        "2025-01-01", "2025-01-15", "2", "2024-12-20",
        "版本20250101", "", "功能,优化", "描述示例",
        "验收标准示例", "业务价值说明"
    ]
    ws.append(sample_row)

    stream = io.BytesIO()
    wb.save(stream)
    stream.seek(0)

    filename = "requirements_template.xlsx"
    headers = {
        "Content-Disposition": f'attachment; filename="{filename}"; filename*=UTF-8\'\'{quote(filename)}'
    }

    return StreamingResponse(
        stream,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers=headers
    )


@router.get("/export", summary="导出需求为Excel")
async def export_requirements(
    request: Request,
    search: Optional[str] = Query(None, description="搜索关键词"),
    project_id: Optional[str] = Query(None, description="项目ID筛选"),
    assignee_id: Optional[str] = Query(None, description="负责人筛选"),
    reporter_id: Optional[str] = Query(None, description="报告人筛选"),
    status: Optional[str] = Query(None, description="状态筛选"),
    priority: Optional[RequirementPriority] = Query(None, description="优先级筛选"),
    requirement_type: Optional[str] = Query(None, description="需求类型筛选（从类型管理获取）"),
    source: Optional[str] = Query(None, description="需求来源筛选（从来源管理获取）"),
    version: Optional[str] = Query(None, description="版本筛选"),
    version_id: Optional[str] = Query(None, description="版本ID筛选"),
    created_at_start: Optional[str] = Query(None, description="创建时间开始"),
    created_at_end: Optional[str] = Query(None, description="创建时间结束"),
    has_version: Optional[bool] = Query(None, description="是否已关联版本"),
    requirement_ids: Optional[str] = Query(None, description="需求ID列表（逗号分隔，如果提供则只导出这些需求）"),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    user_permissions = current_user.get("permissions", [])
    if Permissions.REQUIREMENT_READ not in user_permissions and Permissions.ADMIN not in user_permissions:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_403_FORBIDDEN', 403),
            detail="Not enough permissions to export requirements"
        )

    # 调用服务层方法，所有参数转换逻辑都在service层处理
    requirements = await requirement_service.get_requirements(
        user=current_user,
        skip=0,
        limit=100000,
        project_id=project_id,
        assignee_id=assignee_id,
        reporter_id=reporter_id,
        status=status,
        priority=priority,
        requirement_type=requirement_type,
        source=source,
        created_at_start=created_at_start,
        created_at_end=created_at_end,
        has_version=has_version,
        version_id=version_id
    )

    # 转换为字典格式
    requirement_dicts = requirement_service._convert_requirements_to_dict(requirements)

    if search:
        keyword = search.lower()
        requirement_dicts = [
            req for req in requirement_dicts
            if keyword in (req.get("title") or "").lower()
            or keyword in (req.get("description") or "").lower()
            or keyword in (req.get("project_name") or "").lower()
        ]

    if version and not version_id:
        version_lower = version.lower()
        requirement_dicts = [
            req for req in requirement_dicts
            if any(
                (ver.get("name") or "").lower() == version_lower
                or (ver.get("version_number") or "").lower() == version_lower
                for ver in (req.get("versions") or [])
            )
        ]
    
    # 如果指定了 requirement_ids，只导出这些需求
    if requirement_ids:
        try:
            from bson import ObjectId
            requirement_id_list = [rid.strip() for rid in requirement_ids.split(",") if rid.strip()]
            if requirement_id_list:
                requirement_id_set = set()
                requirement_id_obj_set = set()
                for rid in requirement_id_list:
                    try:
                        requirement_id_set.add(rid)
                        requirement_id_obj_set.add(ObjectId(rid))
                    except Exception:
                        requirement_id_set.add(rid)
                
                requirement_dicts = [
                    req for req in requirement_dicts
                    if (
                        str(req.get("id") or req.get("_id")) in requirement_id_set
                        or (req.get("id") or req.get("_id")) in requirement_id_obj_set
                    )
                ]
        except Exception as e:
            # 如果解析 requirement_ids 失败，记录错误但不阻止导出
            pass

    wb = Workbook()
    ws = wb.active
    ws.title = "需求列表"

    ws.append(REQUIREMENT_EXPORT_HEADERS)

    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="1F2937", end_color="1F2937", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")

    for col_num, header in enumerate(REQUIREMENT_EXPORT_HEADERS, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        ws.column_dimensions[cell.column_letter].width = max(18, len(header) + 6)

    for req in requirement_dicts:
        versions = req.get("versions") or []
        version_names = ", ".join(
            filter(None, [(ver.get("name") or ver.get("version_number") or "") for ver in versions])
        )
        version_ids = ", ".join(
            filter(None, [str(ver.get("id") or ver.get("_id") or "") for ver in versions])
        )
        tag_text = ", ".join(req.get("tags") or [])

        row_data = [
            str(req.get("id") or req.get("_id") or ""),
            req.get("title", ""),
            req.get("description", ""),
            req.get("project_name", ""),
            str(req.get("project_id") or ""),
            req.get("status", ""),
            req.get("priority", ""),
            req.get("requirement_type", ""),
            req.get("source", ""),
            req.get("assignee_name", ""),
            req.get("reporter_name", ""),
            req.get("estimated_effort", ""),
            req.get("start_date", ""),
            req.get("due_date", ""),
            tag_text,
            version_names,
            version_ids,
            req.get("acceptance_criteria", ""),
            req.get("business_value", ""),
            req.get("created_at", ""),
            req.get("updated_at", "")
        ]
        ws.append(row_data)

    stream = io.BytesIO()
    wb.save(stream)
    stream.seek(0)

    filename = f"requirements_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    headers = {
        "Content-Disposition": f'attachment; filename="{filename}"; filename*=UTF-8\'\'{quote(filename)}'
    }

    return StreamingResponse(
        stream,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers=headers
    )


@router.post("/import", response_model=ApiResponse, summary="导入需求（Excel格式）")
async def import_requirements_endpoint(
    file: UploadFile = File(...),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    # 导入功能的权限检查：在创建每个需求时，服务层会检查项目成员权限
    # 这里只做基础的全局权限检查（向后兼容）
    user_permissions = current_user.get("permissions", [])
    if Permissions.REQUIREMENT_CREATE not in user_permissions and Permissions.ADMIN not in user_permissions:
        return error_response(
            error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
            message="Not enough permissions to import requirements",
            code=403
        )
    # 注意：每个需求的实际创建权限会在服务层的 create_requirement 中检查项目成员权限

    if not file.filename or not file.filename.endswith(('.xlsx', '.xls')):
        return error_response(
            error_type=ErrorTypes.INVALID_PARAMETER,
            message="只支持 Excel 文件 (.xlsx, .xls)",
            code=400
        )

    success_count = 0
    error_count = 0
    errors: List[str] = []

    try:
        content = await file.read()
        wb = load_workbook(io.BytesIO(content), data_only=True)
        ws = wb.active

        headers = [cell.value or "" for cell in ws[1]]

        col_map: Dict[str, int] = {}
        for idx, raw_header in enumerate(headers):
            normalized = normalize_import_header(raw_header)
            key = REQUIREMENT_IMPORT_HEADER_KEY_MAP.get(normalized, normalized)
            if key:
                col_map[key] = idx + 1

        required_columns = REQUIREMENT_IMPORT_REQUIRED_KEYS
        missing_columns = [col for col in required_columns if col not in col_map]
        if missing_columns:
            return error_response(
                error_type=ErrorTypes.INVALID_PARAMETER,
                message=f"缺少必需的列: {', '.join(missing_columns)}",
                code=400
            )

        state_list = await requirement_service.get_module_states("requirement")
        status_map = {state.code.lower(): state.code for state in state_list}
        status_name_map = {getattr(state, "name", "").lower(): state.code for state in state_list if getattr(state, "name", "")}
        
        # 状态别名映射 - 从状态管理系统动态生成
        status_alias_map = {}
        from app.utils.state_utils import get_state_code_by_name_keywords
        for state in state_list:
            if state.name:
                # 使用状态名称作为别名
                status_alias_map[state.name] = state.code
                # 添加一些常见的中文别名
                if "新建" in state.name or "new" in state.code.lower():
                    status_alias_map["新建"] = state.code
                if "草稿" in state.name or "draft" in state.code.lower():
                    status_alias_map["草稿"] = state.code
                if "待审核" in state.name or "review" in state.code.lower():
                    status_alias_map["待审核"] = state.code
                if "已批准" in state.name or "approved" in state.code.lower():
                    status_alias_map["已批准"] = state.code
                if "已实现" in state.name or "implemented" in state.code.lower():
                    status_alias_map["已实现"] = state.code
                if "已拒绝" in state.name or "rejected" in state.code.lower():
                    status_alias_map["已拒绝"] = state.code
                if "已取消" in state.name or "cancelled" in state.code.lower():
                    status_alias_map["已取消"] = state.code
                if "进行中" in state.name or "in_progress" in state.code.lower():
                    status_alias_map["进行中"] = state.code
                    status_alias_map["处理中"] = state.code
                if "已完成" in state.name or "completed" in state.code.lower():
                    status_alias_map["已完成"] = state.code

        priority_map = {
            "低": RequirementPriority.LOW.value,
            "中": RequirementPriority.MEDIUM.value,
            "中等": RequirementPriority.MEDIUM.value,
            "高": RequirementPriority.HIGH.value,
            "严重": RequirementPriority.CRITICAL.value,
            "紧急": RequirementPriority.CRITICAL.value
        }

        # 从数据库获取类型和来源映射（从统一配置管理获取）
        type_docs = await db.types.find({"module_type": "requirement", "is_enabled": True}).to_list(length=None)
        type_map = {doc["name"]: doc["code"] for doc in type_docs}
        # 添加代码到代码的映射（如果Excel中直接使用代码）
        for doc in type_docs:
            type_map[doc["code"]] = doc["code"]

        source_docs = await db.sources.find({"module_type": "requirement", "is_enabled": True}).to_list(length=None)
        source_map = {doc["name"]: doc["code"] for doc in source_docs}
        # 添加代码到代码的映射（如果Excel中直接使用代码）
        for doc in source_docs:
            source_map[doc["code"]] = doc["code"]

        current_user_id = ObjectId(current_user.get("user_id"))

        for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=False), start=2):
            try:
                if not any(cell.value for cell in row):
                    continue

                row_data: Dict[str, Any] = {}
                for header_key, col_idx in col_map.items():
                    cell_value = row[col_idx - 1].value if col_idx <= len(row) else None
                    if isinstance(cell_value, str):
                        cell_value = cell_value.strip()
                    row_data[header_key] = cell_value

                title = str(row_data.get("标题", "") or "").strip()
                if not title:
                    error_count += 1
                    errors.append(f"第{row_idx}行: 标题不能为空")
                    continue

                # 优先使用项目ID，如果没有则使用项目名称
                project_id_input = str(row_data.get("项目ID", "") or "").strip()
                project_name = str(row_data.get("项目", "") or "").strip()
                
                project = None
                if project_id_input:
                    try:
                        project_id_obj = ObjectId(project_id_input)
                        project = await db.projects.find_one({"_id": project_id_obj})
                        if not project:
                            error_count += 1
                            errors.append(f"第{row_idx}行: 项目ID '{project_id_input}' 不存在")
                            continue
                    except Exception:
                        error_count += 1
                        errors.append(f"第{row_idx}行: 项目ID '{project_id_input}' 格式无效")
                        continue
                elif project_name:
                    project = await db.projects.find_one({"name": project_name})
                    if not project:
                        error_count += 1
                        errors.append(f"第{row_idx}行: 项目 '{project_name}' 不存在")
                        continue
                else:
                    error_count += 1
                    errors.append(f"第{row_idx}行: 项目或项目ID不能为空")
                    continue

                project_id_str = str(project["_id"])
                project_name = project.get("name", project_name)  # 使用实际的项目名称

                reporter_id = current_user_id
                reporter_name = str(row_data.get("报告人", "") or "").strip()
                if reporter_name:
                    reporter_doc = await db.users.find_one({"name": reporter_name})
                    if reporter_doc:
                        reporter_id = reporter_doc["_id"]

                assignee_id = None
                assignee_name = str(row_data.get("负责人", "") or "").strip()
                if assignee_name:
                    assignee_doc = await db.users.find_one({"name": assignee_name})
                    if not assignee_doc:
                        error_count += 1
                        errors.append(f"第{row_idx}行: 负责人 '{assignee_name}' 不存在")
                        continue
                    assignee_id = assignee_doc["_id"]

                status_value = str(row_data.get("状态", "") or "").strip().lower()
                if status_value in status_alias_map:
                    status_value = status_alias_map[status_value]
                status_code = status_map.get(status_value)
                if not status_code and status_value in status_name_map:
                    status_code = status_name_map[status_value]
                # 从状态管理系统获取默认状态
                from app.utils.state_utils import get_default_state_code
                default_status = await get_default_state_code(db, "requirement")
                status_code = status_code or default_status or ""

                priority_value = str(row_data.get("优先级", "") or "").strip().lower()
                priority_code = priority_map.get(priority_value) or RequirementPriority.MEDIUM.value
                if priority_value in [p.value for p in RequirementPriority]:
                    priority_code = priority_value

                requirement_type_value = str(row_data.get("需求类型", "") or "").strip()
                # 先尝试按名称匹配，再尝试按代码匹配，最后使用默认值
                requirement_type_code = type_map.get(requirement_type_value) or type_map.get(requirement_type_value.lower()) or "functional"

                source_value = str(row_data.get("需求来源", "") or "").strip()
                # 先尝试按名称匹配，再尝试按代码匹配，最后使用默认值
                source_code = source_map.get(source_value) or source_map.get(source_value.lower()) or "internal"

                def parse_date(value):
                    if not value:
                        return None
                    if isinstance(value, datetime):
                        return value
                    value_str = str(value).strip()
                    if not value_str:
                        return None
                    try:
                        return datetime.strptime(value_str, "%Y-%m-%d")
                    except ValueError:
                        try:
                            return datetime.fromisoformat(value_str.replace('Z', '+00:00'))
                        except ValueError:
                            return None

                start_date = parse_date(row_data.get("开始日期(YYYY-MM-DD)"))
                due_date = parse_date(row_data.get("截止日期(YYYY-MM-DD)"))

                estimated_effort = None
                effort_value = row_data.get("预估工时(人天)")
                if effort_value not in (None, ""):
                    try:
                        estimated_effort = float(effort_value)
                    except (ValueError, TypeError):
                        estimated_effort = None

                tags_value = str(row_data.get("标签(逗号分隔)", "") or "")
                tags_list = [tag.strip() for tag in tags_value.split(",") if tag.strip()]

                created_at_value = parse_date(row_data.get("创建时间(YYYY-MM-DD)"))

                # 优先使用版本ID，如果没有则使用版本名称
                version_ids_input = str(row_data.get("版本ID(逗号分隔)", "") or "").strip()
                versions_value = str(row_data.get("关联版本(逗号分隔)", "") or "")
                version_id_list = []
                
                # 如果提供了版本ID，直接使用
                if version_ids_input:
                    version_id_strs = [vid.strip() for vid in version_ids_input.split(",") if vid.strip()]
                    for version_id_str in version_id_strs:
                        try:
                            version_id_obj = ObjectId(version_id_str)
                            version_doc = await db.versions.find_one({"_id": version_id_obj})
                            if not version_doc:
                                error_count += 1
                                errors.append(f"第{row_idx}行: 版本ID '{version_id_str}' 不存在")
                                version_id_list = []
                                break
                            # 验证版本是否属于指定项目
                            if version_doc.get("project_id") != project["_id"]:
                                error_count += 1
                                errors.append(f"第{row_idx}行: 版本ID '{version_id_str}' 不属于项目 '{project_name}'")
                                version_id_list = []
                                break
                            version_id_list.append(version_id_obj)
                        except Exception:
                            error_count += 1
                            errors.append(f"第{row_idx}行: 版本ID '{version_id_str}' 格式无效")
                            version_id_list = []
                            break
                # 如果没有版本ID，使用版本名称查找
                elif versions_value:
                    version_names = [name.strip() for name in versions_value.split(",") if name.strip()]
                    if version_names:
                        for version_name in version_names:
                            version_doc = await db.versions.find_one({
                                "project_id": project["_id"],
                                "$or": [
                                    {"name": version_name},
                                    {"version_number": version_name}
                                ]
                            })
                            if not version_doc:
                                error_count += 1
                                errors.append(f"第{row_idx}行: 项目 '{project_name}' 中未找到版本 '{version_name}'")
                                version_id_list = []
                                break
                            version_id_list.append(version_doc["_id"])
                
                # 如果版本ID列表为空但有版本输入，跳过该行
                if not version_id_list and (version_ids_input or versions_value):
                    continue

                requirement_payload = {
                    "title": title,
                    "description": row_data.get("描述") or "",
                    "requirement_type": requirement_type_code,
                    "priority": priority_code,
                    "source": source_code,
                    "status": status_code,
                    "project_id": project_id_str,
                    "assignee_id": str(assignee_id) if assignee_id else None,
                    "reporter_id": str(reporter_id),
                    "acceptance_criteria": row_data.get("验收标准") or "",
                    "business_value": row_data.get("业务价值") or "",
                    "estimated_effort": estimated_effort,
                    "start_date": start_date.isoformat() if start_date else None,
                    "due_date": due_date.isoformat() if due_date else None,
                    "tags": tags_list
                }

                requirement_response = await requirement_service.create_requirement(
                    user=current_user,
                    requirement_data=requirement_payload
                )

                if hasattr(requirement_response, "model_dump"):
                    requirement_data = requirement_response.model_dump()
                elif isinstance(requirement_response, dict):
                    requirement_data = requirement_response
                else:
                    requirement_data = requirement_response.__dict__

                requirement_id = requirement_data.get("id")
                if version_id_list and requirement_id:
                    requirement_obj_id = ObjectId(requirement_id)
                    now = datetime.utcnow()
                    for version_obj_id in version_id_list:
                        await db.version_requirement_relations.insert_one({
                            "version_id": version_obj_id,
                            "requirement_id": requirement_obj_id,
                            "relation_type": RelationType.INCLUDED.value,
                            "notes": None,
                            "created_by": current_user_id,
                            "created_at": now,
                            "updated_at": now
                        })

                if created_at_value and requirement_id:
                    created_at_utc = created_at_value
                    await db.requirements.update_one(
                        {"_id": ObjectId(requirement_id)},
                        {"$set": {"created_at": created_at_utc, "updated_at": created_at_utc}}
                    )

                success_count += 1
            except Exception as exc:
                error_count += 1
                errors.append(f"第{row_idx}行: {str(exc)}")

    except Exception as exc:
        return error_response(
            error_type=ErrorTypes.INVALID_PARAMETER,
            message=f"导入失败: {str(exc)}",
            code=400
        )

    return success_response(
        data={
            "success_count": success_count,
            "error_count": error_count,
            "errors": errors
        },
        message="导入完成"
    )

@router.get("/{requirement_id}", response_model=None)
async def get_requirement(
    request: Request,
    requirement_id: str,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """获取需求详情"""
    
    try:
        # 验证需求ID格式
        if not requirement_id or requirement_id in ['undefined', 'null', '']:
            return error_response(
                error_type=ErrorTypes.VALIDATION_ERROR,
                message="无效的需求ID格式",
                code=400,
                request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
            )
        
        # 使用服务层方法获取需求详情
        requirement = await requirement_service.get_requirement(
            user=current_user,
            requirement_id=requirement_id
        )
        
        # 将 Pydantic 模型转换为字典
        if hasattr(requirement, 'model_dump'):
            # Pydantic v2
            requirement_data = requirement.model_dump()
        elif hasattr(requirement, 'dict'):
            # Pydantic v1
            requirement_data = requirement.dict()
        else:
            requirement_data = requirement
        
        return success_response(
            data=requirement_data,
            message="获取需求详情成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"获取需求详情失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.post("/", response_model=None)
async def create_requirement(
    request: Request,
    requirement_in: RequirementCreate,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """创建需求"""
    
    try:
        # 将 Pydantic 模型转换为字典
        if hasattr(requirement_in, 'model_dump'):
            # Pydantic v2
            requirement_data = requirement_in.model_dump()
        else:
            # Pydantic v1
            requirement_data = requirement_in.dict()
        
        # 调用服务层方法创建需求
        requirement_response = await requirement_service.create_requirement(
            user=current_user,
            requirement_data=requirement_data
        )
        
        # 将 Pydantic 模型转换为字典
        if hasattr(requirement_response, 'model_dump'):
            # Pydantic v2
            response_data = requirement_response.model_dump()
        elif hasattr(requirement_response, 'dict'):
            # Pydantic v1
            response_data = requirement_response.dict()
        else:
            response_data = requirement_response
        
        # 触发通知：需求创建时通知项目成员和负责人
        try:
            requirement_id = response_data.get("id") if isinstance(response_data, dict) else getattr(requirement_response, 'id', None)
            if requirement_id:
                db = requirement_service.db
                requirement = await db.requirements.find_one({"_id": ObjectId(requirement_id)})
                if requirement:
                    current_user_obj = await db.users.find_one({"_id": ObjectId(current_user.get("user_id"))})
                    current_user_name = current_user_obj.get("name") or current_user_obj.get("username", "系统") if current_user_obj else "系统"
                    requirement_title = requirement.get("title", "需求")
                    
                    # 获取项目信息
                    project_id = requirement.get("project_id")
                    project = None
                    if project_id:
                        project = await db.projects.find_one({"_id": project_id})
                    
                    # 收集需要通知的用户ID
                    notify_user_ids = set()
                    
                    # 添加负责人
                    assignee_id = requirement.get("assignee_id")
                    if assignee_id:
                        notify_user_ids.add(assignee_id)
                    
                    # 添加项目成员（排除当前用户）
                    if project:
                        members = project.get("members", [])
                        user_id = ObjectId(current_user.get("user_id"))
                        for member in members:
                            member_user_id = member.get("user_id")
                            if member_user_id and member_user_id != user_id:
                                notify_user_ids.add(member_user_id)
                    
                    # 发送通知
                    notification_tasks = []
                    for user_id_to_notify in notify_user_ids:
                        if user_id_to_notify != ObjectId(current_user.get("user_id")):
                            notification_data = NotificationCreate(
                                user_id=str(user_id_to_notify),
                                type=NotificationType.REQUIREMENT_CREATED,
                                title=f"新需求创建：{requirement_title}",
                                content=f"{current_user_name} 创建了需求「{requirement_title}」",
                                related_type=RelatedObjectType.REQUIREMENT,
                                related_id=str(requirement_id)
                            )
                            notification_tasks.append(
                                NotificationService.create_notification(
                                    db=db,
                                    notification_data=notification_data,
                                    send_email=False
                                )
                            )
                    
                    # 异步发送所有通知
                    if notification_tasks:
                        asyncio.create_task(asyncio.gather(*notification_tasks, return_exceptions=True))
        except Exception as e:
            logger.warning(f"发送需求创建通知失败: {str(e)}")
        
        return success_response(
            data=response_data,
            message="创建需求成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"创建需求失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.put("/{requirement_id}", response_model=None)
async def update_requirement(
    request: Request,
    requirement_id: str,
    requirement_in: RequirementUpdate,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """更新需求"""
    
    try:
        # 调用服务层方法更新需求
        # 处理 Pydantic v1 和 v2 的兼容性
        if hasattr(requirement_in, 'model_dump'):
            # Pydantic v2
            update_data = requirement_in.model_dump(exclude_unset=True)
        else:
            # Pydantic v1
            update_data = requirement_in.dict(exclude_unset=True)
        
        requirement_response = await requirement_service.update_requirement(
            user=current_user,
            requirement_id=requirement_id,
            update_data=update_data
        )
        
        # 将 Pydantic 模型转换为字典
        if hasattr(requirement_response, 'model_dump'):
            # Pydantic v2
            response_data = requirement_response.model_dump()
        elif hasattr(requirement_response, 'dict'):
            # Pydantic v1
            response_data = requirement_response.dict()
        else:
            response_data = requirement_response
        
        # 触发通知：需求更新时通知项目成员、负责人和创建者
        try:
            db = requirement_service.db
            requirement = await db.requirements.find_one({"_id": ObjectId(requirement_id)})
            if requirement:
                current_user_id = ObjectId(current_user.get("user_id"))
                current_user_obj = await db.users.find_one({"_id": current_user_id})
                current_user_name = current_user_obj.get("name") or current_user_obj.get("username", "系统") if current_user_obj else "系统"
                requirement_title = requirement.get("title", "需求")
                
                # 获取项目信息
                project_id = requirement.get("project_id")
                project = None
                if project_id:
                    project = await db.projects.find_one({"_id": project_id})
                
                # 收集需要通知的用户ID（使用ObjectId确保类型一致）
                notify_user_ids = set()
                
                # 添加负责人（如果存在且不是当前用户）
                assignee_id = requirement.get("assignee_id")
                if assignee_id:
                    assignee_obj_id = assignee_id if isinstance(assignee_id, ObjectId) else ObjectId(assignee_id)
                    if assignee_obj_id != current_user_id:
                        notify_user_ids.add(assignee_obj_id)
                
                # 添加创建者（如果存在且不是当前用户）
                created_by = requirement.get("created_by")
                if created_by:
                    created_by_obj_id = created_by if isinstance(created_by, ObjectId) else ObjectId(created_by)
                    if created_by_obj_id != current_user_id:
                        notify_user_ids.add(created_by_obj_id)
                
                # 添加项目成员（排除当前用户）
                if project:
                    members = project.get("members", [])
                    for member in members:
                        member_user_id = member.get("user_id")
                        if member_user_id:
                            member_obj_id = member_user_id if isinstance(member_user_id, ObjectId) else ObjectId(member_user_id)
                            if member_obj_id != current_user_id:
                                notify_user_ids.add(member_obj_id)
                
                # 发送通知
                notification_tasks = []
                for user_id_to_notify in notify_user_ids:
                    notification_data = NotificationCreate(
                        user_id=str(user_id_to_notify),
                        type=NotificationType.REQUIREMENT_UPDATED,
                        title=f"需求更新：{requirement_title}",
                        content=f"{current_user_name} 更新了需求「{requirement_title}」",
                        related_type=RelatedObjectType.REQUIREMENT,
                        related_id=requirement_id
                    )
                    notification_tasks.append(
                        NotificationService.create_notification(
                            db=db,
                            notification_data=notification_data,
                            send_email=False
                        )
                    )
                
                # 异步发送所有通知
                if notification_tasks:
                    asyncio.create_task(asyncio.gather(*notification_tasks, return_exceptions=True))
        except Exception as e:
            logger.warning(f"发送需求更新通知失败: {str(e)}")
        
        return success_response(
            data=response_data,
            message="更新需求成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"更新需求失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.delete("/{requirement_id}", response_model=None)
async def delete_requirement(
    request: Request,
    requirement_id: str,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """删除需求"""
    
    try:
        # 获取删除前的需求信息（用于通知）
        db = requirement_service.db
        requirement = await db.requirements.find_one({"_id": ObjectId(requirement_id)})
        
        # 调用服务层方法删除需求
        await requirement_service.delete_requirement(
            user=current_user,
            requirement_id=requirement_id
        )
        
        # 触发通知：需求删除时通知项目成员和负责人
        if requirement:
            try:
                current_user_obj = await db.users.find_one({"_id": ObjectId(current_user.get("user_id"))})
                current_user_name = current_user_obj.get("name") or current_user_obj.get("username", "系统") if current_user_obj else "系统"
                requirement_title = requirement.get("title", "需求")
                
                # 获取项目信息
                project_id = requirement.get("project_id")
                project = None
                if project_id:
                    project = await db.projects.find_one({"_id": project_id})
                
                # 收集需要通知的用户ID
                notify_user_ids = set()
                
                # 添加负责人
                assignee_id = requirement.get("assignee_id")
                if assignee_id:
                    notify_user_ids.add(assignee_id)
                
                # 添加项目成员（排除当前用户）
                if project:
                    members = project.get("members", [])
                    user_id = ObjectId(current_user.get("user_id"))
                    for member in members:
                        member_user_id = member.get("user_id")
                        if member_user_id and member_user_id != user_id:
                            notify_user_ids.add(member_user_id)
                
                # 发送通知
                notification_tasks = []
                for user_id_to_notify in notify_user_ids:
                    if user_id_to_notify != ObjectId(current_user.get("user_id")):
                        notification_data = NotificationCreate(
                            user_id=str(user_id_to_notify),
                            type=NotificationType.REQUIREMENT_DELETED,
                            title=f"需求已删除：{requirement_title}",
                            content=f"{current_user_name} 删除了需求「{requirement_title}」",
                            related_type=RelatedObjectType.REQUIREMENT,
                            related_id=requirement_id
                        )
                        notification_tasks.append(
                            NotificationService.create_notification(
                                db=db,
                                notification_data=notification_data,
                                send_email=False
                            )
                        )
                
                # 异步发送所有通知
                if notification_tasks:
                    asyncio.create_task(asyncio.gather(*notification_tasks, return_exceptions=True))
            except Exception as e:
                logger.warning(f"发送需求删除通知失败: {str(e)}")
        
        return success_response(
            message="需求删除成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"删除需求失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.patch("/{requirement_id}/status", response_model=None)
async def update_requirement_status(
    request: Request,
    requirement_id: str,
    status_update: RequirementStatusUpdate,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """更新需求状态"""
    
    try:
        # 调用服务层方法更新需求状态
        result = await requirement_service.update_requirement_status(
            user=current_user,
            requirement_id=requirement_id,
            status_update=status_update
        )
        
        # 将 Pydantic 模型转换为字典
        if hasattr(result, 'model_dump'):
            # Pydantic v2
            response_data = result.model_dump()
        elif hasattr(result, 'dict'):
            # Pydantic v1
            response_data = result.dict()
        else:
            response_data = result
        
        # 触发通知：需求状态更新时通知项目成员、负责人和创建者
        try:
            db = requirement_service.db
            requirement = await db.requirements.find_one({"_id": ObjectId(requirement_id)})
            if requirement:
                current_user_id = ObjectId(current_user.get("user_id"))
                current_user_obj = await db.users.find_one({"_id": current_user_id})
                current_user_name = current_user_obj.get("name") or current_user_obj.get("username", "系统") if current_user_obj else "系统"
                requirement_title = requirement.get("title", "需求")
                
                # 获取状态名称
                new_status = status_update.status if hasattr(status_update, 'status') else status_update.get('status', '')
                state_doc = await db.states.find_one({
                    "module_type": "requirement",
                    "code": new_status
                })
                status_name = state_doc.get("name", new_status) if state_doc else new_status
                
                # 获取项目信息
                project_id = requirement.get("project_id")
                project = None
                if project_id:
                    project = await db.projects.find_one({"_id": project_id})
                
                # 收集需要通知的用户ID（使用ObjectId确保类型一致）
                notify_user_ids = set()
                
                # 添加负责人（如果存在且不是当前用户）
                assignee_id = requirement.get("assignee_id")
                if assignee_id:
                    assignee_obj_id = assignee_id if isinstance(assignee_id, ObjectId) else ObjectId(assignee_id)
                    if assignee_obj_id != current_user_id:
                        notify_user_ids.add(assignee_obj_id)
                
                # 添加创建者（如果存在且不是当前用户）
                created_by = requirement.get("created_by")
                if created_by:
                    created_by_obj_id = created_by if isinstance(created_by, ObjectId) else ObjectId(created_by)
                    if created_by_obj_id != current_user_id:
                        notify_user_ids.add(created_by_obj_id)
                
                # 添加项目成员（排除当前用户）
                if project:
                    members = project.get("members", [])
                    for member in members:
                        member_user_id = member.get("user_id")
                        if member_user_id:
                            member_obj_id = member_user_id if isinstance(member_user_id, ObjectId) else ObjectId(member_user_id)
                            if member_obj_id != current_user_id:
                                notify_user_ids.add(member_obj_id)
                
                # 发送通知
                notification_tasks = []
                for user_id_to_notify in notify_user_ids:
                    notification_data = NotificationCreate(
                        user_id=str(user_id_to_notify),
                        type=NotificationType.REQUIREMENT_UPDATED,
                        title=f"需求状态更新：{requirement_title}",
                        content=f"{current_user_name} 将需求「{requirement_title}」的状态更新为「{status_name}」",
                        related_type=RelatedObjectType.REQUIREMENT,
                        related_id=requirement_id
                    )
                    notification_tasks.append(
                        NotificationService.create_notification(
                            db=db,
                            notification_data=notification_data,
                            send_email=False
                        )
                    )
                
                # 异步发送所有通知
                if notification_tasks:
                    asyncio.create_task(asyncio.gather(*notification_tasks, return_exceptions=True))
        except Exception as e:
            logger.warning(f"发送需求状态更新通知失败: {str(e)}")
        
        return success_response(
            data=response_data,
            message="更新需求状态成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"更新需求状态失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.get("/{requirement_id}/attachments", response_model=ApiResponse, summary="获取需求附件列表")
async def get_requirement_attachments(
    requirement_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    try:
        requirement_object_id = ObjectId(requirement_id)
    except Exception:
        return error_response(
            error_type=ErrorTypes.INVALID_PARAMETER,
            message="Invalid requirement ID format",
            code=400
        )

    requirement = await db.requirements.find_one({"_id": requirement_object_id})
    if not requirement:
        return error_response(
            error_type=ErrorTypes.NOT_FOUND,
            message="Requirement not found",
            code=404
        )

    # 检查项目成员权限
    project_id = requirement.get("project_id")
    if project_id:
        from app.services.permission_service import PermissionService
        try:
            project_id_str = str(project_id) if isinstance(project_id, ObjectId) else project_id
            has_access = await PermissionService.check_project_member_permission(
                project_id_str,
                current_user,
                db,
                "requirement:read"
            )
            if not has_access:
                return error_response(
                    error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                    message="Not enough permissions to access this requirement",
                    code=403
                )
        except Exception:
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message="Not enough permissions to access this requirement",
                code=403
            )
    else:
        # 如果没有项目ID，使用旧的权限检查（向后兼容）
        user_permissions = current_user.get("permissions", [])
        try:
            user_id = ObjectId(current_user.get("user_id"))
        except Exception:
            user_id = None

        has_access = (
            Permissions.REQUIREMENT_READ_ALL in user_permissions or
            Permissions.ADMIN in user_permissions or
            (requirement.get("reporter_id") == user_id if user_id else False) or
            (requirement.get("assignee_id") == user_id if user_id else False)
        )

        watchers = requirement.get("watchers", [])
        if not has_access and watchers and user_id:
            has_access = any(
                watcher == user_id or str(watcher) == str(user_id)
                for watcher in watchers
            )

        if not has_access:
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message="Not enough permissions to access this requirement",
                code=403
            )

    attachments = requirement.get("attachments", [])
    formatted_attachments = []
    for attachment in attachments:
        uploaded_at = attachment.get("uploaded_at")
        if isinstance(uploaded_at, datetime):
            uploaded_at_str = uploaded_at.isoformat()
        elif uploaded_at:
            uploaded_at_str = str(uploaded_at)
        else:
            uploaded_at_str = None

        formatted_attachments.append({
            "id": str(attachment.get("id")),
            "filename": attachment.get("filename", ""),
            "original_filename": attachment.get("original_filename", ""),
            "file_size": attachment.get("file_size", 0),
            "file_type": attachment.get("file_type", ""),
            "url": attachment.get("url", ""),
            "uploaded_by": str(attachment.get("uploaded_by")) if attachment.get("uploaded_by") else None,
            "uploaded_at": uploaded_at_str
        })

    return success_response(
        data=formatted_attachments,
        message="获取附件列表成功"
    )

@router.post("/{requirement_id}/attachments", response_model=ApiResponse, summary="上传需求附件")
async def upload_requirement_attachment(
    requirement_id: str,
    file: UploadFile = File(...),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    try:
        requirement_object_id = ObjectId(requirement_id)
    except Exception:
        return error_response(
            error_type=ErrorTypes.INVALID_PARAMETER,
            message="Invalid requirement ID format",
            code=400
        )

    requirement = await db.requirements.find_one({"_id": requirement_object_id})
    if not requirement:
        return error_response(
            error_type=ErrorTypes.NOT_FOUND,
            message="Requirement not found",
            code=404
        )

    # 检查项目成员权限
    project_id = requirement.get("project_id")
    if project_id:
        from app.services.permission_service import PermissionService
        try:
            project_id_str = str(project_id) if isinstance(project_id, ObjectId) else project_id
            await PermissionService.require_project_member_permission(
                project_id_str,
                current_user,
                db,
                "requirement:update"
            )
        except Exception as e:
            # PermissionError 是内置异常，不需要导入
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message=str(e),
                code=403
            )
    else:
        # 如果没有项目ID，使用旧的权限检查（向后兼容）
        user_permissions = current_user.get("permissions", [])
        try:
            user_id = ObjectId(current_user.get("user_id"))
        except Exception:
            user_id = None

        has_permission = (
            Permissions.REQUIREMENT_UPDATE in user_permissions or
            Permissions.ADMIN in user_permissions or
            (requirement.get("reporter_id") == user_id if user_id else False) or
            (requirement.get("assignee_id") == user_id if user_id else False)
        )

        if not has_permission:
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message="Not enough permissions to upload attachments for this requirement",
                code=403
            )

    try:
        attachment_meta = await attachment_service.upload(
            file=file,
            directory=f"requirements/{requirement_id}",
            filename_prefix=f"requirement_{requirement_id}",
            max_size=getattr(settings, 'MAX_FILE_SIZE', 50 * 1024 * 1024)
        )
    except ValueError as exc:
        return error_response(
            error_type=ErrorTypes.VALIDATION_ERROR,
            message=str(exc),
            code=400
        )
    except Exception as exc:
        return error_response(
            error_type=ErrorTypes.SERVER_ERROR,
            message=f"Failed to upload attachment: {str(exc)}",
            code=500
        )

    attachment = {
        "id": ObjectId(),
        **attachment_meta,
        "url": f"/api/v1/requirements/{requirement_id}/attachments/{attachment_meta['filename']}/download",
        "uploaded_by": user_id,
    }

    await db.requirements.update_one(
        {"_id": requirement_object_id},
        {
            "$push": {"attachments": attachment},
            "$set": {"updated_at": datetime.utcnow()}
        }
    )

    uploaded_at = attachment["uploaded_at"]
    uploaded_at_iso = uploaded_at.isoformat() if isinstance(uploaded_at, datetime) else uploaded_at

    return success_response(
        data={
            "id": str(attachment["id"]),
            "filename": attachment["filename"],
            "original_filename": attachment["original_filename"],
            "file_size": attachment["file_size"],
            "file_type": attachment["file_type"],
            "url": attachment["url"],
            "uploaded_by": str(attachment["uploaded_by"]) if attachment.get("uploaded_by") else None,
            "uploaded_at": uploaded_at_iso
        },
        message="上传附件成功"
    )

@router.delete("/{requirement_id}/attachments/{attachment_id}", response_model=ApiResponse, summary="删除需求附件")
async def delete_requirement_attachment(
    requirement_id: str,
    attachment_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    try:
        requirement_object_id = ObjectId(requirement_id)
        attachment_object_id = ObjectId(attachment_id)
    except Exception:
        return error_response(
            error_type=ErrorTypes.INVALID_PARAMETER,
            message="Invalid ID format",
            code=400
        )

    requirement = await db.requirements.find_one({"_id": requirement_object_id})
    if not requirement:
        return error_response(
            error_type=ErrorTypes.NOT_FOUND,
            message="Requirement not found",
            code=404
        )

    # 检查项目成员权限
    project_id = requirement.get("project_id")
    if project_id:
        from app.services.permission_service import PermissionService
        try:
            project_id_str = str(project_id) if isinstance(project_id, ObjectId) else project_id
            await PermissionService.require_project_member_permission(
                project_id_str,
                current_user,
                db,
                "requirement:update"
            )
        except Exception as e:
            # PermissionError 是内置异常，不需要导入
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message=str(e),
                code=403
            )
    else:
        # 如果没有项目ID，使用旧的权限检查（向后兼容）
        user_permissions = current_user.get("permissions", [])
        try:
            user_id = ObjectId(current_user.get("user_id"))
        except Exception:
            user_id = None

        has_permission = (
            Permissions.REQUIREMENT_UPDATE in user_permissions or
            Permissions.ADMIN in user_permissions or
            (requirement.get("reporter_id") == user_id if user_id else False) or
            (requirement.get("assignee_id") == user_id if user_id else False)
        )

        if not has_permission:
            return error_response(
                error_type=ErrorTypes.PERMISSION_DENIED_ERROR,
                message="Not enough permissions to delete attachments for this requirement",
                code=403
            )

    attachments = requirement.get("attachments", [])
    attachment = next((a for a in attachments if a.get("id") == attachment_object_id), None)

    if not attachment:
        return error_response(
            error_type=ErrorTypes.NOT_FOUND,
            message="Attachment not found",
            code=404
        )

    file_path = attachment.get("filename")
    if file_path:
        attachment_service.delete(directory=f"requirements/{requirement_id}", filename=file_path)

    await db.requirements.update_one(
        {"_id": requirement_object_id},
        {
            "$pull": {"attachments": {"id": attachment_object_id}},
            "$set": {"updated_at": datetime.utcnow()}
        }
    )

    return success_response(message="删除附件成功")

@router.get("/{requirement_id}/attachments/{filename}/download", summary="下载需求附件")
async def download_requirement_attachment(
    requirement_id: str,
    filename: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    try:
        requirement_object_id = ObjectId(requirement_id)
    except Exception:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_400_BAD_REQUEST', 400),
            detail="Invalid requirement ID format"
        )

    requirement = await db.requirements.find_one({"_id": requirement_object_id})
    if not requirement:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_404_NOT_FOUND', 404),
            detail="Requirement not found"
        )

    attachments = requirement.get("attachments", [])
    attachment = next((a for a in attachments if a.get("filename") == filename), None)
    if not attachment:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_404_NOT_FOUND', 404),
            detail="Attachment not found"
        )

    # 检查项目成员权限
    project_id = requirement.get("project_id")
    if project_id:
        from app.services.permission_service import PermissionService
        try:
            project_id_str = str(project_id) if isinstance(project_id, ObjectId) else project_id
            has_access = await PermissionService.check_project_member_permission(
                project_id_str,
                current_user,
                db,
                "requirement:read"
            )
            if not has_access:
                raise HTTPException(
                    status_code=getattr(http_status_module, 'HTTP_403_FORBIDDEN', 403),
                    detail="Not enough permissions to access this attachment"
                )
        except Exception:
            raise HTTPException(
                status_code=getattr(http_status_module, 'HTTP_403_FORBIDDEN', 403),
                detail="Not enough permissions to access this attachment"
            )
    else:
        # 如果没有项目ID，使用旧的权限检查（向后兼容）
        user_permissions = current_user.get("permissions", [])
        try:
            user_id = ObjectId(current_user.get("user_id"))
        except Exception:
            user_id = None

        has_access = (
            Permissions.REQUIREMENT_READ_ALL in user_permissions or
            Permissions.ADMIN in user_permissions or
            (requirement.get("reporter_id") == user_id if user_id else False) or
            (requirement.get("assignee_id") == user_id if user_id else False)
        )

        watchers = requirement.get("watchers", [])
        if not has_access and watchers and user_id:
            has_access = any(
                watcher == user_id or str(watcher) == str(user_id)
                for watcher in watchers
            )

        if not has_access:
            raise HTTPException(
                status_code=getattr(http_status_module, 'HTTP_403_FORBIDDEN', 403),
                detail="Not enough permissions to access this attachment"
            )

    try:
        file_content, content_type = attachment_service.download(
            directory=f"requirements/{requirement_id}",
            filename=filename
        )
    except FileNotFoundError:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_404_NOT_FOUND', 404),
            detail="Attachment not found"
        )
    except Exception as exc:
        raise HTTPException(
            status_code=getattr(http_status_module, 'HTTP_500_INTERNAL_SERVER_ERROR', 500),
            detail=f"Failed to download file: {str(exc)}"
        )

    download_filename = attachment.get("original_filename", filename) or filename
    try:
        download_filename.encode('ascii')
        content_disposition = f'attachment; filename="{download_filename}"'
    except UnicodeEncodeError:
        encoded_filename = quote(download_filename.encode('utf-8'), safe='')
        content_disposition = f'attachment; filename="{filename}"; filename*=UTF-8\'\'{encoded_filename}'

    return StreamingResponse(
        io.BytesIO(file_content),
        media_type=content_type or attachment.get("file_type", "application/octet-stream"),
        headers={"Content-Disposition": content_disposition}
    )



@router.get("/board/{project_id}", response_model=None)
async def get_requirement_board(
    request: Request,
    project_id: str,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """获取需求看板数据"""
    
    try:
        # 调用服务层方法获取需求看板数据
        board_data = await requirement_service.get_requirement_board(
            user=current_user,
            project_id=project_id
        )
        
        return success_response(
            data=board_data,
            message="获取需求看板成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"获取需求看板失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.get("/stats/overview", response_model=None)
async def get_requirement_stats(
    request: Request,
    project_id: Optional[str] = Query(None, description="项目ID筛选"),
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """获取需求统计数据"""
    
    try:
        # 调用服务层方法获取需求统计数据
        stats = await requirement_service.get_requirement_stats(
            user=current_user,
            project_id=project_id
        )
        
        # 将 Pydantic 模型转换为字典
        if hasattr(stats, 'model_dump'):
            # Pydantic v2
            stats_data = stats.model_dump()
        elif hasattr(stats, 'dict'):
            # Pydantic v1
            stats_data = stats.dict()
        else:
            stats_data = stats
        
        return success_response(
            data=stats_data,
            message="获取需求统计成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"获取需求统计失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

@router.post("/bulk", response_model=None)
async def bulk_update_requirements(
    request: Request,
    bulk_update: RequirementBulkUpdate,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """批量更新需求"""
    
    try:
        # 调用服务层方法批量更新需求
        # 处理 Pydantic v1 和 v2 的兼容性
        if hasattr(bulk_update.updates, 'model_dump'):
            # Pydantic v2
            update_data = bulk_update.updates.model_dump(exclude_unset=True)
        else:
            # Pydantic v1
            update_data = bulk_update.updates.dict(exclude_unset=True)
        
        result = await requirement_service.bulk_update_requirements(
            user=current_user,
            requirement_ids=bulk_update.requirement_ids,
            update_data=update_data
        )
        
        return success_response(
            data=result,
            message="批量更新需求成功",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"批量更新需求失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

class RequirementBulkDelete(BaseModel):
    """批量删除需求模型"""
    requirement_ids: TypingList[str] = Field(..., min_items=1, description="需求ID列表")

@router.delete("/bulk", response_model=None)
async def bulk_delete_requirements(
    request: Request,
    bulk_delete: RequirementBulkDelete,
    current_user: dict = Depends(get_current_user),
    requirement_service: RequirementService = Depends(get_requirement_service)
) -> Any:
    """批量删除需求"""
    
    try:
        if not bulk_delete.requirement_ids:
            return error_response(
                error_type=ErrorTypes.VALIDATION_ERROR,
                message="需求ID列表不能为空",
                code=400,
                request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
            )
        
        # 逐个删除需求（使用服务层的删除方法以确保权限检查）
        deleted_count = 0
        errors = []
        
        for req_id in bulk_delete.requirement_ids:
            try:
                await requirement_service.delete_requirement(
                    user=current_user,
                    requirement_id=req_id
                )
                deleted_count += 1
            except Exception as e:
                errors.append(f"需求 {req_id}: {str(e)}")
        
        if errors:
            return error_response(
                error_type=ErrorTypes.INTERNAL_ERROR,
                message=f"部分删除失败，成功删除 {deleted_count} 个，失败 {len(errors)} 个",
                details={"errors": errors, "deleted_count": deleted_count},
                request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
            )
        
        return success_response(
            data={"deleted_count": deleted_count},
            message=f"成功删除 {deleted_count} 个需求",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type=ErrorTypes.INTERNAL_ERROR,
            message=f"批量删除需求失败: {str(e)}",
            request_id=getattr(request.state, 'request_id', None) if hasattr(request, 'state') else None
        )

